package com.shop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.shop.bean.Orders;
import com.shop.bean.User;
import com.shop.dao.IUserDao;
import com.shop.util.ConnDB;

/** 
 * @className: UserDaoImpl.java
 * @classDescription: 
 * @function: 
 * @author: Wentasy
 * @createTime: 2012-11-1 下午07:17:49
 * @modifyTime: 
 * @modifyReason: 
 * @since: JDK 1.6
 */
public class UserDaoImpl implements IUserDao{
	
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	private String sql = "";
	private int affectedLine = 0;
	private User user = null;
	public int createUser(User user) throws Exception {
		 String userAccount = user.getUserAccount();
		 boolean userSex = user.getUserSex();
		 String password = user.getPassword();
		 String name = user.getName();
		 int userGrade = user.getUserGrade();
		 String phone = user.getPhone();
		 String email = user.getEmail();
		 Boolean flag = user.getFlag();
		 
		 conn = ConnDB.getConn();
		 sql = "insert into user " +
		 		"(userAccount,userSex,password,name,userGrade,phone,email,falg) values(?,?,?,?,?,?,?,?)"; 
		 pstmt = conn.prepareStatement(sql);
		 pstmt.setString(1,userAccount);
		 pstmt.setBoolean(2,userSex);
		 pstmt.setString(3,password);
		 pstmt.setString(4,name);
		 pstmt.setInt(5,userGrade);
		 pstmt.setString(6, phone);
		 pstmt.setString(7, email);
		 pstmt.setBoolean(8, flag);
		 
		 affectedLine = pstmt.executeUpdate();
		 ConnDB.close(pstmt);
		 ConnDB.close(conn);
		return affectedLine;
		 	
		
	}

	public int deleteUserById(long userId) throws Exception {
		conn = ConnDB.getConn();
		sql = "delete from user where userId = ?";
		pstmt = conn.prepareStatement(sql);
		pstmt.setLong(1, userId);
		affectedLine = pstmt.executeUpdate();
		ConnDB.close(pstmt);
		ConnDB.close(conn);
		return affectedLine;
	}

	public List<User> queryAllUser() throws Exception {
		sql = "select * from user";
		List<User> userList = new ArrayList<User>();
		conn = ConnDB.getConn();
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		while(rs.next()){
			user = new User();
			user.setUserId(rs.getLong("userId"));
			user.setUserAccount(rs.getString("userAccount"));
			user.setUserSex(rs.getBoolean("userSex"));
			user.setPassword(rs.getString("password"));
			user.setName(rs.getString("name"));
			user.setUserGrade(rs.getInt("userGrade"));
			user.setPhone(rs.getString("phone"));
			user.setEmail(rs.getString("email"));
			user.setFlag(rs.getBoolean("flag"));
			userList.add(user);
			
		}
		return userList;
	}

	public List<User> queryAllUser(int startPage, int pageSize)
			throws Exception {
		sql = "select * from user limit(? - 1) * ?, ?";
		pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, startPage);
		pstmt.setInt(2, pageSize);
		pstmt.setInt(3, pageSize);
		List<User> userList = new ArrayList<User>();
		rs = pstmt.executeQuery();
		while(rs.next()){
			user = new User();
			user.setUserId(rs.getLong("userId"));
			user.setUserAccount(rs.getString("userAccount"));
			user.setUserSex(rs.getBoolean("userSex"));
			user.setPassword(rs.getString("password"));
			user.setName(rs.getString("name"));
			user.setUserGrade(rs.getInt("userGrade"));
			user.setPhone(rs.getString("phone"));
			user.setEmail(rs.getString("email"));
			user.setFlag(rs.getBoolean("flag"));
			userList.add(user);
		}
		return userList;
	}

	public User queryUserById(long userId) throws Exception {
		conn = ConnDB.getConn();
		sql = "select * from user where userId =?";
		pstmt = conn.prepareStatement(sql);
		pstmt.setLong(1, userId);
		rs = pstmt.executeQuery();
		if(rs.next()){
			user = new User();
			user.setUserId(rs.getLong("userId"));
			user.setUserAccount(rs.getString("userAccount"));
			user.setUserSex(rs.getBoolean("userSex"));
			user.setPassword(rs.getString("password"));
			user.setName(rs.getString("name"));
			user.setUserGrade(rs.getInt("userGrade"));
			user.setPhone(rs.getString("phone"));
			user.setEmail(rs.getString("email"));
			user.setFlag(rs.getBoolean("flag"));
		}
		return user;
	}

	public User queryUserByName(String name) throws Exception {
		
		conn = ConnDB.getConn();
		sql = "select * from user where name =?";
		pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, name);
		rs = pstmt.executeQuery();
		if(rs.next()){
			user = new User();
			user.setUserId(rs.getLong("userId"));
			user.setUserAccount(rs.getString("userAccount"));
			user.setUserSex(rs.getBoolean("userSex"));
			user.setPassword(rs.getString("password"));
			user.setName(rs.getString("name"));
			user.setUserGrade(rs.getInt("userGrade"));
			user.setPhone(rs.getString("phone"));
			user.setEmail(rs.getString("email"));
			user.setFlag(rs.getBoolean("flag"));
		}
		return user;
	}

	public int updateOrder(User user) throws Exception {
		Long userId = user.getUserId();
		String userAccount = user.getUserAccount();
		Boolean userSex = user.getUserSex();
		String password = user.getPassword();
		String name = user.getName();
		int userGrade = user.getUserGrade();
		String phone = user.getPhone();
		String email = user.getEmail();
		Boolean flag = user.getFlag();
		
		conn = ConnDB.getConn();
		sql ="update user set userAccount=?,userSex=?,password=?,name=?,userGrade=?,phone=?,email=?,flag=?" +
			  "where userId = ?";
		pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, userAccount);
		pstmt.setBoolean(2, userSex);
		pstmt.setString(3, password);
		pstmt.setString(4, name);
		pstmt.setInt(5, userGrade);
		pstmt.setString(6,phone);
		pstmt.setString(7,email);
		pstmt.setBoolean(8, flag);
		affectedLine = pstmt.executeUpdate();
		return affectedLine;
	}
	
}
